package com.gbase8c.dmt.db.opengauss;

import com.gbase8c.dmt.db.meta.ExtTypes;
import com.gbase8c.dmt.db.object.IndexObject;
import com.gbase8c.dmt.db.object.TableObject;

import com.gbase8c.dmt.model.migration.config.DataTypeConfig;
import com.gbase8c.dmt.model.migration.config.TableTypeConfig;
import com.gbase8c.dmt.model.migration.config.Task;
import com.gbase8c.dmt.model.migration.dto.*;
import com.gbase8c.dmt.model.migration.wrapper.DataTypeWrapper;
import com.gbase8c.dmt.model.migration.wrapper.KeywordsWrapper;
import com.gbase8c.dmt.model.migration.wrapper.TableTypeWrapper;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Slf4j
public class TableObjectImpl extends MetaImpl implements TableObject {

    protected IndexObject indexObject;

    public TableObjectImpl(DataSourceDto dataSourceDto) {
        super(dataSourceDto);
        indexObject = new IndexObjectImpl(dataSourceDto);
    }

    @Override
    public List<String> getNames(Map<String, Object> params) {
        String schema = (String) params.get("schema");
        String sql = "SELECT tablename FROM pg_tables WHERE schemaname = ?";
        return query(sql, new ColumnListHandler<String>(), schema);
    }

    @Override
    public TableDto get(String name, Map<String, Object> params) {
        String schema = (String) params.get("schema");

        String sql = "SELECT table_schema as schema, table_name, column_name as name, data_type," +
                " character_maximum_length as length, numeric_precision as data_precision," +
                " numeric_scale as data_scale, is_nullable as nullable " +
                "FROM information_schema.columns WHERE table_schema = ? and table_name = ? order by ordinal_position";

        String pkSql = "select pg_attribute.attname as colname\n" +
                "from pg_constraint inner join pg_class\n" +
                "on pg_constraint.conrelid = pg_class.oid\n" +
                "inner join pg_attribute on pg_attribute.attrelid = pg_class.oid\n" +
                "and pg_attribute.attnum = any(pg_constraint.conkey)\n" +
                "where pg_class.relname = ? \n" +
                "and pg_constraint.contype = 'p'";

        BeanProcessor beanProcessor = new GenerousBeanProcessor();
        RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
        List<TableDto.ColumnDto> columnDtos = query(sql, new BeanListHandler<>(TableDto.ColumnDto.class, rowProcessor), schema, name);

        //查询主键
        List<String> pkList = query(pkSql, new ColumnListHandler<String>(), name);

        for (TableDto.ColumnDto columnDto : columnDtos) {
            columnDto.setSqlType(toSqlType(columnDto.getDataType()));

            //主键
            if (pkList.contains(columnDto.getName())) {
                columnDto.setPk(true);
            }
        }

        TableDto tableDto = TableDto.builder()
                .ds(dataSourceDto.getId())
                .schema(schema)
                .name(name)
                .columnDtos(columnDtos)
                .build();
        return tableDto;
    }

    @Override
    public TableDto convert(TableDto tableDto, Map<String, Object> params) {
        Task task = tableDto.getTask();
        TableTypeWrapper tableTypeWrapper = new TableTypeWrapper(task.getTableTypeConfig());
        DataTypeWrapper dataTypeWrapper = new DataTypeWrapper(task.getDataTypeConfig());
        KeywordsWrapper keywordsWrapper = new KeywordsWrapper(task.getKeywordsConfig());
        boolean preserveCase = task.getMigrateConfig().isPreserveCase();

        String tarTableName = keywordsWrapper.getTableName(tableDto.getSchema(), tableDto.getName(), preserveCase);
//        tableDto.setTarSchema(preserveCase ? tableDto.getSchema() : tableDto.getSchema().toLowerCase());
        tableDto.setTarName(tarTableName);

        if ("YES".equalsIgnoreCase(tableDto.getProperties().getOrDefault("partitioned", "NO").toString())) {
            String tarPartitionColumn = keywordsWrapper.getColumnName(tableDto.getSchema(),
                    tableDto.getName(),
                    tableDto.getProperties().get("PARTITIONCOLUMN".toUpperCase()).toString(),
                    preserveCase);
            tableDto.getProperties().put("PARTITIONCOLUMN".toUpperCase(), tarPartitionColumn);
        }

        List<TableDto.ColumnDto> scaleLessThan0Col = Lists.newArrayList();
        // 列: 列名, 类型
        List<TableDto.ColumnDto> columnDtos = tableDto.getColumnDtos();
        columnDtos.forEach(columnDto -> {
            String tarColumnName = keywordsWrapper.getColumnName(tableDto.getSchema(),
                    tableDto.getName(),
                    columnDto.getName(),
                    preserveCase);
            columnDto.setTarName(tarColumnName);
            DataTypeConfig.DataTypeMapper dataTypeMapper =
                    dataTypeWrapper.dataTypeMapper(columnDto.getDataType(), columnDto.getDataLength(),
                            columnDto.getDataPrecision(), columnDto.getDataScale());
            if (ObjectUtils.isNotEmpty(dataTypeMapper)) {
                columnDto.setTarDataType(dataTypeMapper.getTarDataType());
                columnDto.setTarDataLength(dataTypeMapper.getTarDataLength());
                columnDto.setTarDataPrecision(dataTypeMapper.getTarDataPrecision());
                columnDto.setTarDataScale(dataTypeMapper.getTarDataScale());
            } else {
                String tarDataType = toDataType(columnDto.getSqlType());
                if (StringUtils.isBlank(tarDataType)) {
                    throw new RuntimeException(String.format("未找到对应的字段映射类型: %s", columnDto.getDataType()));
                } else {
                    columnDto.setTarDataType(tarDataType);
                    columnDto.setTarDataLength(columnDto.getDataLength());
                    columnDto.setTarDataPrecision(columnDto.getDataPrecision());
                    columnDto.setTarDataScale(columnDto.getDataScale());
                }
            }
            if (columnDto.getTarDataScale() != null && columnDto.getTarDataScale() < 0){
                scaleLessThan0Col.add(columnDto);
            }
        });

        if (CollectionUtils.isNotEmpty(scaleLessThan0Col)) {
            List<TriggerDto> triggerDtos = processScaleLessThan0(task,tableDto.getTarSchema(),tableDto.getTarName(),scaleLessThan0Col);
            tableDto.setTriggerDtos(triggerDtos);
        }
        tableDto.setConvertible(Boolean.TRUE);
        return tableDto;
    }

    @Override
    public String sql(TableDto tableDto, Map<String, Object> params) {
        return null;
    }

    //当分区方式为range时，使用less than语句构建
    private String getDefaultPartedValue(String partitionColumn, List<TableDto.ColumnDto> columnDtos) {
        // from (x) to (z)
        TableDto.ColumnDto partColumn = null;
        for (TableDto.ColumnDto columnDto : columnDtos) {
            String columnName = columnDto.getName().toLowerCase();
            if (columnName.equals(partitionColumn.toLowerCase())) {
                partColumn = columnDto;
            }
        }
        assert partColumn != null;
        Integer sqlType = partColumn.getSqlType();
        if (Types.SMALLINT == sqlType || Types.INTEGER == sqlType || Types.BIGINT == sqlType) {
            return "partition p1 values less than (1),partition p2 values less than (100)";
        } else if (Types.NUMERIC == sqlType || Types.FLOAT == sqlType || Types.DOUBLE == sqlType) {
            return "partition p1 values less than (1.0),partition p2 values less than (100.0)";
        } else {
            return "partition p1 values less than (1),partition p2 values less than (100)";
        }
    }

    //当分区方式为list时，使用In 语句构建
    private String getListDefaultPartedValue(String partitionColumn, List<TableDto.ColumnDto> columnDtos) {
        // from (x) to (z)
        TableDto.ColumnDto partColumn = null;
        for (TableDto.ColumnDto columnDto : columnDtos) {
            String columnName = columnDto.getName().toLowerCase();
            if (columnName.equals(partitionColumn.toLowerCase())) {
                partColumn = columnDto;
            }
        }
        assert partColumn != null;
        Integer sqlType = partColumn.getSqlType();
//        if (Types.SMALLINT == sqlType || Types.INTEGER == sqlType || Types.BIGINT == sqlType) {
//            return "IN (1,2,3,4,5,6,7,8,9,10,100)";
//        } else if (Types.NUMERIC == sqlType || Types.FLOAT == sqlType || Types.DOUBLE == sqlType) {
//            return "IN (1.0,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,100.0)";
//        } else if (Types.CHAR == sqlType || Types.VARCHAR == sqlType) {
//            return "IN ('a','b','c','d','e')";
//        } else if (Types.DATE == sqlType) {
//            return "IN ('2021-01-01','2022-01-01')";
//        } else if (Types.TIME == sqlType) {
//            return "IN ('00:00:00','12:00:00')";
//        } else {
//            return "IN (1,2,3,4,5,6,7,8,9,10,100)";
//        }
        if (Types.SMALLINT == sqlType || Types.INTEGER == sqlType || Types.BIGINT == sqlType) {
            return "partition p1 values(1),partition p2 values(2),partition p3 values(3),partition p4 values(4)";
        } else if (Types.NUMERIC == sqlType || Types.FLOAT == sqlType || Types.DOUBLE == sqlType) {
            return "partition p1 values(1.0),partition p2 values(2.0),partition p3 values(3.0),partition p4 values(4.0)";
        } else if (Types.CHAR == sqlType || Types.VARCHAR == sqlType) {
            return "partition p1 values('a'),partition p2 values('b'),partition p3 values('c'),partition p4 values('d')";
        } else if (Types.DATE == sqlType) {
            return "partition p1 values('2021-01-01'),partition p2 values('2022-01-01')";
        } else if (Types.TIME == sqlType) {
            return "partition p1 values('00:00:00'),partition p2 values('12:00:00')";
        } else {
            return "partition p1 values(1),partition p2 values(2),partition p3 values(3),partition p4 values(4)";
        }
    }

    //当分区方式为hash时，不需要value值
    private String getHashDefaultPartedValue(String partitionColumn, List<TableDto.ColumnDto> columnDtos) {
        // from (x) to (z)
        TableDto.ColumnDto partColumn = null;
        for (TableDto.ColumnDto columnDto : columnDtos) {
            String columnName = columnDto.getName().toLowerCase();
            if (columnName.equals(partitionColumn.toLowerCase())) {
                partColumn = columnDto;
            }
        }
        assert partColumn != null;
        Integer sqlType = partColumn.getSqlType();
        return "partition p1 ,partition p2 ,partition p3 ,partition p4 ";

    }

    //当分区方式为range时,源库的表也为分区表时，使用less than语句构建
    private String getPartedValue(List<PartitionInfoDto> partitionInfos, Map<String, Object> properties) {
        StringBuilder stringBuilder = new StringBuilder();
        //partition p1 values less than ('2022-02-01 00:00:00'),
        // partition p2 values less than ('2023-02-01 00:00:00')
        if (CollectionUtils.isNotEmpty(partitionInfos)) {
            for (PartitionInfoDto partitionInfoDto:partitionInfos){
                stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values less than (");
                String subPartValue = null;
                for (PartitionInfoDto.PartColumnDto partColumnDto : partitionInfoDto.getPartColumnDtoList()) {
                    String dataType = partColumnDto.getDataType();
                    if (dataType.equals("DATE") || dataType.contains("TIMESTAMP") || dataType.equals("date")
                            || dataType.equals("year") || dataType.equals("datetime") || dataType.equals("timestamp") || dataType.equals("time")) {
                        if (CollectionUtils.isNotEmpty(partitionInfoDto.getSubPartitionDtoList())) {
                            String partitionColumn = properties.get("PARTITIONCOLUMN").toString();
                            List<String> partitionColumnList = Arrays.asList(partitionColumn.split(","));
                            String subColumnName = properties.get("SUBCOLUMN_NAME").toString();
                            List<String> subColumnNameList = Arrays.asList(subColumnName.split(","));
                            if (partitionColumnList.size() == 1 && subColumnNameList.size() == 1) {
                                subPartValue = getSubPartedValue(partitionInfoDto.getSubPartitionDtoList());
                            }
                        }
                        if (partColumnDto.getValue().toString().equalsIgnoreCase("NULL")) {
                            partColumnDto.setValue("default");
                        }
                        String value = partColumnDto.getValue().toString();
                        if (!value.equals("MAXVALUE")) {
//                        stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values less than ")
                            stringBuilder.append("\'").append(value).append("\'");
//                            if (null != subPartValue) {
//                                stringBuilder.append(subPartValue);
//                            }
                            stringBuilder.append(",");
                        } else if (value.equals("MAXVALUE")) {
//                        stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values less than ")
                            stringBuilder.append(value);
//                            if (null != subPartValue) {
//                                stringBuilder.append(subPartValue);
//                            }
                            stringBuilder.append(",");
                        }
                    } else {
                        if (CollectionUtils.isNotEmpty(partitionInfoDto.getSubPartitionDtoList())) {
                            String partitionColumn = properties.get("PARTITIONCOLUMN").toString();
                            List<String> partitionColumnList = Arrays.asList(partitionColumn.split(","));
                            String subColumnName = properties.get("SUBCOLUMN_NAME").toString();
                            List<String> subColumnNameList = Arrays.asList(subColumnName.split(","));
                            if (partitionColumnList.size() == 1 && subColumnNameList.size() == 1) {
                                subPartValue = getSubPartedValue(partitionInfoDto.getSubPartitionDtoList());
                            }
                        }
                        if (partColumnDto.getHighValue().toString().equalsIgnoreCase("NULL")){
                            partColumnDto.setHighValue("default");
                        }
//                        stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values less than ")
                        stringBuilder.append(partColumnDto.getHighValue());
                        stringBuilder.append(",");
                    }
                }
                String s = stringBuilder.substring(0,stringBuilder.length()-1);
                s = s + ")";
                stringBuilder = new StringBuilder(s);
                if (null != subPartValue){
                    stringBuilder.append(subPartValue);
                }
                stringBuilder.append(",");
            }
        }
        String s = stringBuilder.toString();
        String result = s.substring(0,s.length()-1);
        return result;
    }

    //当分区方式为list时，使用In 语句构建
    private String getListPartedValue(List<PartitionInfoDto> partitionInfos,Map<String, Object> properties) {
        StringBuilder stringBuilder = new StringBuilder();
        //partition p1 values less than ('2022-02-01 00:00:00'),
        // partition p2 values less than ('2023-02-01 00:00:00')
        for (PartitionInfoDto partitionInfoDto:partitionInfos){
            String partitionValue = partitionInfoDto.getValue().toString();
            String dataType = partitionInfoDto.getDataType();
            String subPartValue = null;
            if (CollectionUtils.isNotEmpty(partitionInfoDto.getSubPartitionDtoList())) {
                String partitionColumn = properties.get("PARTITIONCOLUMN").toString();
                List<String> partitionColumnList = Arrays.asList(partitionColumn.split(","));
                String subColumnName = properties.get("SUBCOLUMN_NAME").toString();
                List<String> subColumnNameList = Arrays.asList(subColumnName.split(","));
                if (partitionColumnList.size() == 1 && subColumnNameList.size() == 1) {
                    subPartValue = getSubPartedValue(partitionInfoDto.getSubPartitionDtoList());
                }
            }
            if (partitionValue.equalsIgnoreCase("NULL")){
                partitionInfoDto.setValue("default");
            }
            String partitiondescr = partitionInfoDto.getHighValue().toString();
            String partColumnName = properties.get("PARTITIONCOLUMN").toString();
            List<String> partColumnNameList = Arrays.asList(partColumnName.split(","));
            if (partColumnNameList.size() == 1) {
                if (dataType.equals("DATE") || dataType.contains("TIMESTAMP") || dataType.equals("date")
                        || dataType.equals("year") || dataType.equals("datetime") || dataType.equals("timestamp")){
                    String srcDesc = partitionInfoDto.getValue().toString();
                    List<String> descList = Arrays.asList(srcDesc.split(","));
                    List<String> descList2 = new ArrayList<>();
                    if (descList.size() == 1 && descList.get(0).equalsIgnoreCase("DEFAULT")) {
                        descList2.add("default");
                    } else {
                        for (String desc : descList) {
                            desc = "\'" + desc + "\'";
                            descList2.add(desc);
                        }
                    }
                    partitiondescr = String.join(",", descList2);
                }
                List<String> list = Arrays.asList(partitiondescr.split(","));
                List<String> list1 = Lists.newArrayList();
                for (int j = 0; j<list.size(); j++){
                    String string = list.get(j);
                    list1.add(string);
                }
                String desc = String.join(",",list1);

                if (partitionValue.equalsIgnoreCase("NULL")){
                    desc = "default";
                }

                stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values ")
                        .append("(").append(desc).append(")");
                if (null != subPartValue){
                    stringBuilder.append(subPartValue);
                }
                stringBuilder.append(",");
            } else {
                stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName()).append(" values ")
                        .append("(").append(partitiondescr).append(")");
                stringBuilder.append(",");
            }
        }

        String s = stringBuilder.toString();
        String result = s.substring(0,s.length()-1);
        return result;
    }

    //当分区方式为hash时，不需要highvalue
    private String getHashPartedValue(List<PartitionInfoDto> partitionInfos,Map<String, Object> properties) {
        StringBuilder stringBuilder = new StringBuilder();
        for (PartitionInfoDto partitionInfoDto:partitionInfos){
            String subPartValue = null;
            if (CollectionUtils.isNotEmpty(partitionInfoDto.getSubPartitionDtoList())) {
                String partitionColumn = properties.get("PARTITIONCOLUMN").toString();
                List<String> partitionColumnList = Arrays.asList(partitionColumn.split(","));
                String subColumnName = properties.get("SUBCOLUMN_NAME").toString();
                List<String> subColumnNameList = Arrays.asList(subColumnName.split(","));
                if (partitionColumnList.size() == 1 && subColumnNameList.size() == 1) {
                    subPartValue = getSubPartedValue(partitionInfoDto.getSubPartitionDtoList());
                }
            }
            stringBuilder.append("partition ").append(partitionInfoDto.getPartitionName());
            if (null != subPartValue){
                stringBuilder.append(subPartValue);
            }
            stringBuilder.append(",");
        }

        String s = stringBuilder.toString();
        String result = s.substring(0,s.length()-1);
        return result;
    }

    //当分区方式为hash时，不需要highvalue
    private String getSubPartedValue(List<PartitionInfoDto.SubPartitionDto> subPartitionDtoList) {
        StringBuilder stringBuilder = new StringBuilder();
        String subDataType = "DATE";
        String subPartitionBy = "RANGE";
        if (CollectionUtils.isNotEmpty(subPartitionDtoList)){
            subDataType = subPartitionDtoList.get(0).getSubDataType();
            subPartitionBy = subPartitionDtoList.get(0).getSubPartitionBy();
        }
        stringBuilder.append("(");
        if (subPartitionBy.equals("RANGE")) {
            if (subDataType.equals("DATE") || subDataType.contains("TIMESTAMP") || subDataType.equals("date")
                    || subDataType.equals("year") || subDataType.equals("datetime") || subDataType.equals("timestamp")){
                for (PartitionInfoDto.SubPartitionDto subPartitionDto:subPartitionDtoList){
                    String subPartValue = null;
//                    if (CollectionUtils.isNotEmpty(partitionInfoDto.getSubPartitionDtoList())) {
//                        subPartValue = getSubPartedValue(partitionInfoDto.getSubPartitionDtoList());
//                    }
                    String subValue = subPartitionDto.getSubValue().toString();
                    if (!subValue.equals("MAXVALUE")) {
                        stringBuilder.append("subpartition ").append(subPartitionDto.getSubPartitionName()).append(" values less than ")
                                .append("(").append("\'").append(subValue).append("\'").append(")");
                        stringBuilder.append(",");
                    } else if (subValue.equals("MAXVALUE")) {
                        stringBuilder.append("subpartition ").append(subPartitionDto.getSubPartitionName()).append(" values less than ")
                                .append("(").append(subValue).append(")");
                        stringBuilder.append(",");
                    }
                }
            } else {
                for (PartitionInfoDto.SubPartitionDto subPartitionDto:subPartitionDtoList){
                    stringBuilder.append("subpartition ").append(subPartitionDto.getSubPartitionName()).append(" values less than ")
                            .append("(").append(subPartitionDto.getSubPartHighValue()).append("),");
                }
            }
        }

        if (subPartitionBy.equals("LIST")) {
            for (PartitionInfoDto.SubPartitionDto subPartitionDto:subPartitionDtoList){
                String subPartitionValue = subPartitionDto.getSubValue().toString();
                subDataType = subPartitionDto.getSubDataType();
                if (subPartitionValue.equalsIgnoreCase("NULL") || subPartitionValue.equalsIgnoreCase("'NULL'")){
                    subPartitionDto.setSubValue("default");
                }
                String subPartitiondescr = subPartitionDto.getSubPartHighValue().toString();
                if (subDataType.equals("DATE") || subDataType.contains("TIMESTAMP") || subDataType.equals("date")
                        || subDataType.equals("year") || subDataType.equals("datetime") || subDataType.equals("timestamp")){
                    String srcDesc = subPartitionDto.getSubValue().toString();
                    List<String> descList = Arrays.asList(srcDesc.split(","));
                    List<String> descList2 = new ArrayList<>();
                    for (String desc : descList) {
                        desc = "\'" + desc + "\'";
                        descList2.add(desc);
                    }
                    subPartitiondescr = String.join(",", descList2);
//                partitiondescr = "\'" + getListDatePartHighValue(partitiondescr) + "\'";
                }
                List<String> list = Arrays.asList(subPartitiondescr.split(","));
                List<String> list1 = Lists.newArrayList();
                for (int j = 0;j<list.size();j++){
                    String string = list.get(j);
                    list1.add(string);
                }
                String desc = String.join(",",list1);

                if (subPartitionValue.equalsIgnoreCase("NULL") || subPartitionValue.equalsIgnoreCase("'NULL'")){
                    desc = "default";
                }

                stringBuilder.append("subpartition ").append(subPartitionDto.getSubPartitionName()).append(" values ")
                        .append("(").append(desc).append("),");
            }
        }

        if (subPartitionBy.contains("HASH") || subPartitionBy.contains("KEY")) {
            for (PartitionInfoDto.SubPartitionDto subPartitionDto:subPartitionDtoList){
                stringBuilder.append("subpartition ").append(subPartitionDto.getSubPartitionName())
                        .append(",");
            }
        }
        String s = stringBuilder.toString();
        String result = s.substring(0,s.length()-1);
        result = result + ")";
        return result;
    }

    @Override
    public List<String> tableSql(TableDto tableDto) {
        StringBuilder sb = new StringBuilder();
        String tarSchemaName = tableDto.getTarSchema();
        String tarTableName = tableDto.getTarName();

        boolean preserveCase = tableDto.getTask().getMigrateConfig().isPreserveCase();
        //处理mysql enum/set类型
        if (CollectionUtils.isNotEmpty(tableDto.getEnumOrSetColumns())) {
            List<TableDto.ColumnDto> enumOrSetColumns = tableDto.getEnumOrSetColumns();
            for (TableDto.ColumnDto enumOrSetColumn : enumOrSetColumns ){
                String typeName = enumOrSetColumn.getDataType() + "_" + tarTableName + "_mode";
                String columnType = enumOrSetColumn.getColumnType();
                //为了支持set，用enum模拟set
                if (enumOrSetColumn.getSqlType().equals(ExtTypes.SET)) {
                    Pattern pattern = Pattern.compile("(?<=\\()[^\\)]+");
                    Matcher matcher = pattern.matcher(columnType);
                    while(matcher.find()){
                        columnType = "enum(" + matcher.group() + ")";
                    }
                }
                sb.append("drop type if exists ").append(wrap(tarSchemaName, true)).append(".")
                        .append(typeName).append(" CASCADE").append(";").append("\r\n");
                sb.append("create type ").append(wrap(tarSchemaName, true)).append(".")
                        .append(typeName).append(" as ").append(columnType).append(";").append("\r\n");
                enumOrSetColumn.setEnumOrSetDataType(typeName);
            }
        }
        sb.append("DROP TABLE IF EXISTS ").append(wrap(tarSchemaName, true))
                .append(".").append(wrap(tarTableName, preserveCase))
                .append(" CASCADE").append(";").append("\r\n");
        sb.append("CREATE ");
        //如果是unlogged表，拼接unlogged属性
        if (!StringUtils.isBlank(tableDto.getRelPersistence())) {
            String relPersistence = tableDto.getRelPersistence();
            if (relPersistence.equals("u")) {
                sb.append(" unlogged ");
            }
        }
        sb.append("TABLE ").append(wrap(tarSchemaName, true))
                .append(".").append(wrap(tarTableName, preserveCase)).append(" (").append("\r\n");
        List<TableDto.ColumnDto> columnDtos = tableDto.getColumnDtos();

        List<String> pkList = new LinkedList<>();

        List<TableDto.ColumnDto> scaleLessThan0Col = Lists.newArrayList();
        // TODO 可能考虑的不全
        //遍历每一个column
        for (int i = 0; i < columnDtos.size(); i++) {
            TableDto.ColumnDto column = columnDtos.get(i);
            String tarColumnName = column.getTarName();
            sb.append(wrap(tarColumnName, preserveCase)).append(" ");
            String dataType = column.getTarDataType();
            Integer sqlType = column.getSqlType();
            if (StringUtils.isNotBlank(dataType) && dataType.equals("interval")) {
                dataType = column.getDataType();
            }
            // todo 一些非常用数据类型可能没有sqlType, 需做非空判断
            if (ObjectUtils.isNotEmpty(column.getSqlType())) {
                if (column.getSqlType().equals(ExtTypes.ENUM) || column.getSqlType().equals(ExtTypes.SET)) {
                    dataType = wrap(tarSchemaName, true) + "." + column.getEnumOrSetDataType();
                }
            }
            //获取主键名称
            if (column.isPk()) {
                pkList.add(column.getName());
            }

            if(timeTypeSet.contains(sqlType)){
                Integer dataScale = column .getTarDataScale();
                if (dataScale != null)
                {
                    List<String> strings = Arrays.stream(dataType.split(" ")).collect(Collectors.toList());
                    if(CollectionUtils.isNotEmpty(strings)){
                        String timestampScale = strings.get(0) + "("+ dataScale + ")";
                        strings.set(0,timestampScale);
                    }
                    sb.append(String.join(" ",strings));
                }
            } else {
                sb.append(dataType);
                if (needSetLengthSet.contains(dataType)) {

                    //如果说是numeric类型，是这个样子的 "a23" numeric(255,6)
                    if (dataType.equals("numeric")) {
                        Integer dataPrecision = column.getTarDataPrecision();
                        Integer dataScale = column.getTarDataScale();
                        if (!(Objects.isNull(dataScale)) && !(Objects.isNull(dataPrecision))
                                && dataScale > dataPrecision){
                            dataScale = dataPrecision;
                        }
                        if (dataPrecision != null) {
                            if (dataScale != null && dataScale < 0){
                                sb.append("(").append(dataPrecision - dataScale);
//                            if (dataScale != null) {
                                sb.append(", ").append(0);
//                            }
                                sb.append(")");
                                scaleLessThan0Col.add(column);
                            } else {
                                sb.append("(").append(dataPrecision);
                                if (dataScale != null) {
                                    sb.append(", ").append(dataScale);
                                }
                                sb.append(")");
                            }
                        }
                    } else {
                        //不是numeric字段的 是这个样子的 "a12" varbit(255)
//                    Integer dataLength = column.getDataLength();
                        if (column.getTarDataLength() != null) {
                            sb.append("(").append(column.getTarDataLength()).append(")").append(" ");
                        }
                    }
                }
            }

            //不为空的字段
            if ("NO".equalsIgnoreCase(column.getNullable())) {
                sb.append(" ").append("NOT NULL").append(" ");
            }

            //默认值
            if (!StringUtils.isBlank(column.getDataDefault())) {
                String dataDefault = column.getDataDefault();
                if (!preserveCase) {
                    //match double quotes
                    Pattern p = Pattern.compile("\"[^\"]*\"");
                    Matcher m = p.matcher(dataDefault);
                    List<String> list = Lists.newArrayList();
                    while(m.find()){
                        list.add(m.group());
                    }
                    if (CollectionUtils.isNotEmpty(list)){
                        for (String s : list) {
                            dataDefault = dataDefault.replace(s,s.toLowerCase());
                        }
                    }
                }
                if (dataDefault.startsWith("'") && dataDefault.endsWith("'")){
                    dataDefault = dataDefault.substring(1,dataDefault.length()-1);
                    if (dataDefault.contains("\'")) {
                        dataDefault = dataDefault.replaceAll("\'","\'\'");
                    }
                    dataDefault = "\'" + dataDefault + "\'";
                }
                // todo 暂时处理一下current_timestamp()
                if ("current_timestamp()".equalsIgnoreCase(dataDefault)) {
                    dataDefault = "current_timestamp";
                }
                sb.append(" default ").append(dataDefault);
            }

            //句尾逗号
            if (i < columnDtos.size() - 1) {
                sb.append(", \r\n");
            }
        }

        sb.append(") \r\n");

        //拼gauss的table options 例：orientation=column,compression=high
        String tableOptions = tableDto.getTableOptions();
        if (!StringUtils.isBlank(tableOptions)) {
            sb.append("with( ");
            sb.append(tableOptions);
            sb.append(")");
        }

        //建立在对应表空间
        if (tableDto.getTableSpaceName()!=null && tableDto.getProperties().get("ISTOTABLESPACE").equals("YES")){
            sb.append(" tablespace ").append(wrap(tableDto.getTableSpaceName(), preserveCase)).append(" ");
        }

        StringBuilder partitionSql = new StringBuilder();
        String dbType = query("select version()", new ScalarHandler<String>());
        Map<String, Object> properties = tableDto.getProperties();

        if (properties != null && !properties.isEmpty()) {
            String partitioned = (String) properties.get("PARTITIONED");
            if ("YES".equals(partitioned)) {
                String partSql = processParted(tableDto);
                sb.append(partSql);
            }
        }

        sb.append(";\r\n");

        String tableComment = tableDto.getTableComment();
        if (!StringUtils.isBlank(tableComment)) {
            sb.append("COMMENT ON TABLE ");
            sb.append(wrap(tarSchemaName,true));
            sb.append(".");
            sb.append(wrap(tarTableName,preserveCase));
            sb.append(" is ");
            sb.append("\'" + tableComment + "\'");
            sb.append(";\r\n");
        }

        //列的注释
        if (CollectionUtils.isNotEmpty(columnDtos)) {
            for (TableDto.ColumnDto columnDto : columnDtos) {
                if (!StringUtils.isBlank(columnDto.getColumnComment())) {
                    String columnComment = columnDto.getColumnComment();
                    columnComment = columnComment.replaceAll("\'","\'\'");
                    sb.append("COMMENT ON COLUMN ");
                    sb.append(wrap(tarSchemaName,true)).append(".");
                    sb.append(wrap(tarTableName,preserveCase)).append(".");
                    sb.append(wrap(columnDto.getName(),preserveCase));
                    sb.append(" is ").append("\'" + columnComment + "\'").append(";\r\n");
                }
            }
        }

        if (tableDto.getAutoColumn() != null) {
            TableDto.ColumnDto columnDto = tableDto.getAutoColumn();
            String autoIncrement = columnDto.getAutoIncrement();
            String sequenceName = tarSchemaName + tarTableName + "_increment_ ";
            sb.append("set search_path to ").append(wrap(tarSchemaName,true)).append(";\r\n");
            sb.append("create sequence ").append(sequenceName)
                    .append(" start ").append(autoIncrement).append(" owned by ")
                    .append(wrap(tarTableName,preserveCase)).append(".")
                    .append(wrap(columnDto.getName(),preserveCase)).append(";\r\n");
            sb.append("alter table ").append(wrap(tarTableName,preserveCase))
                    .append(" alter column ").append(wrap(columnDto.getName(),preserveCase))
                    .append(" set default nextval('").append(sequenceName).append("')").append(";\r\n");
        }

        sb.append("alter table ");
        sb.append(wrap(tarSchemaName,true));
        sb.append(".");
        sb.append(wrap(tarTableName,preserveCase));
        sb.append(" set(parallel_workers=8)");
        sb.append(";\r\n");

        ArrayList<String> sqls = Lists.newArrayList(sb.toString());

        if(dbType.contains("multiple_nodes")){
            if (partitionSql.length() != 0)
                sqls.add(partitionSql.toString());
        }
        sqls.forEach(log::info);
        return sqls;
    }

    @Override
    public List<TableTypeMapperDto> tableTypeMapper(String schema) {
        return null;
    }

    @Override
    public BigDecimal dataSize(String schema, String tableName) {
        return null;
    }

    public String  getDatePartHighValue(String columnName){
        Pattern p = Pattern.compile("'(.*?)'");
        Matcher m = p.matcher(columnName);
        List<String> list = Lists.newArrayList();
        String result = null;
        while(m.find()){
            list.add(m.group().trim().replace("'",""));
        }
        if (CollectionUtils.isNotEmpty(list)){
            result = list.get(0);
        }
        return result;
    }

    public String  getListDatePartHighValue(String columnName){
        Pattern p = Pattern.compile("\\d{4}-\\d{2}-]]d{2}");
        Matcher m = p.matcher(columnName);
        List<String> list = Lists.newArrayList();
        String result = null;
        while(m.find()){
            list.add(m.group().trim().replace("'",""));
        }
        if (CollectionUtils.isNotEmpty(list)){
            result = list.get(0);
        }
        return result;
    }

    public String processParted(TableDto tableDto){
        StringBuilder sb = new StringBuilder();
        Map<String, Object> properties = tableDto.getProperties();
        boolean preserveCase = tableDto.getTask().getMigrateConfig().isPreserveCase();
        List<PartitionInfoDto> partitionInfos = tableDto.getPartitionInfos();
        String partitionBy = (String) properties.get("PARTITIONBY");

        if (partitionBy.contains("HASH")) {
            partitionBy = "HASH";
        }
        if (partitionBy.contains("RANGE")) {
            partitionBy = "RANGE";
        }
        if (partitionBy.contains("LIST")) {
            partitionBy = "LIST";
        }
        if (partitionBy.equals("RANGE") || partitionBy.equals("RANGE COLUMNS") || partitionBy.equals("LIST") || partitionBy.contains("HASH")) {
            String partitionColumn = (String) properties.get("PARTITIONCOLUMN");
            //将列以逗号拆分.
            List<String> columnList = Arrays.asList(org.apache.commons.lang.StringUtils.split(partitionColumn, ","));
            //拆分后单独处理各列名
            List<String> columnListTemp = Lists.newArrayList();
            for (String column : columnList) {
                column = wrap(column, preserveCase);
                columnListTemp.add(column);
            }

            //处理后重新以逗号拼接
            String columnNames = columnListTemp.stream().collect(Collectors.joining(","));

            if (CollectionUtils.isNotEmpty(partitionInfos)) {
                //TODO 如果是分区表,就必须有一个或多个分区子表才能插入数据，暂时写死
                //String defaultPartedSql = getDefaultPartedSql(partitionColumn, partitionBy, tableDto);
                String defaultPartedValue = null;
                if (partitionBy.equals("RANGE") || partitionBy.equals("RANGE COLUMNS")) {
                    defaultPartedValue = getPartedValue(partitionInfos,properties);
                } else if (partitionBy.equals("LIST")) {
                    defaultPartedValue = getListPartedValue(partitionInfos,properties);
                } else if (partitionBy.contains("HASH")) {
                    defaultPartedValue = getHashPartedValue(partitionInfos,properties);
                }
                sb.append("PARTITION BY ").append(partitionBy.toLowerCase()).append("(").append(columnNames).append(")").append(" \r\n");
//                if (properties.containsKey("INTERVALSTR")) {
//                    sb.append(" interval ").append(properties.get("INTERVALSTR"));
//                }
                if (properties.containsKey("SUBPARTITION_TYPE") && properties.containsKey("SUBCOLUMN_NAME")){
                    String partitionColumnName = properties.get("PARTITIONCOLUMN").toString();
                    List<String> partitionColumnNameList = Arrays.asList(partitionColumnName.split(","));
                    String subColumnName = properties.get("SUBCOLUMN_NAME").toString();
                    List<String> subColumnNameList = Arrays.asList(subColumnName.split(","));
                    if (partitionColumnNameList.size() == 1 && subColumnNameList.size() == 1) {
                        String subPartitionBy = properties.get("SUBPARTITION_TYPE").toString();
                        if (subPartitionBy.contains("KEY")) {
                            subPartitionBy = "HASH";
                        }
                        sb.append("SUBPARTITION BY ").append(subPartitionBy.toLowerCase()).append("(").append(subColumnName).append(")").append(" \r\n");
                    }
                }
                sb.append("( ").append(defaultPartedValue).append(")").append(" \r\n");
                //partitionSql.append(defaultPartedSql);
            } else {
                //TODO 如果是分区表,就必须有一个或多个分区子表才能插入数据，暂时写死
                //String defaultPartedSql = getDefaultPartedSql(partitionColumn, partitionBy, tableDto);
                String defaultPartedValue = null;
                if (partitionBy.equals("RANGE")) {
                    defaultPartedValue = getDefaultPartedValue(partitionColumn, tableDto.getColumnDtos());
                } else if (partitionBy.equals("LIST")) {
                    defaultPartedValue = getListDefaultPartedValue(partitionColumn, tableDto.getColumnDtos());
                } else if (partitionBy.equals("HASH")) {
                    defaultPartedValue = getHashDefaultPartedValue(partitionColumn, tableDto.getColumnDtos());
                }
                sb.append("PARTITION BY ").append(partitionBy.toLowerCase()).append("(").append(partitionColumn.toLowerCase()).append(")").append(" \r\n");
                sb.append("( ").append(defaultPartedValue).append(")").append(" \r\n");
                //partitionSql.append(defaultPartedSql);
            }
        }
        return sb.toString();
    }

    public List<TriggerDto> processScaleLessThan0(Task task,String tarSchema,String tarTable,List<TableDto.ColumnDto> scaleLessThan0Col) {
        List<TriggerDto> triggerDtos = Lists.newArrayList();
        Boolean preservCase  = task.getMigrateConfig().isPreserveCase();
        StringBuilder roundDataSb = new StringBuilder();
        for (TableDto.ColumnDto columnDto : scaleLessThan0Col) {
            Integer tarScale = columnDto.getTarDataScale();
            roundDataSb.append("new.").append(wrap(columnDto.getTarName(),preservCase))
                    .append(" := round(new.").append(wrap(columnDto.getTarName(),preservCase))
                    .append(",").append(tarScale).append(");\n");
        }
        String eventObjectSchema = tarSchema;
        String eventObjectTable = tarTable;
        String tarTriggerBody = "begin\n" + roundDataSb +
//                                   "\tnew.t1 := round(new.t1,-2);\n" +
                                   "RETURN NEW;\n" +
                                "END";
        String schema = scaleLessThan0Col.get(0).getSchema();
        String tarName = eventObjectSchema + "_" + eventObjectTable + "_insert_trigger";
        String srcName = eventObjectSchema + "_" + eventObjectTable + "_insert_trigger" +
                "(" + eventObjectSchema + "." + eventObjectTable + ")";
        TriggerDto triggerDto = TriggerDto.builder()
                .task(task)
                .schema(schema)
                .tarSchema(tarSchema)
                .tarName(tarName)
                .name(srcName)
                .actionTiming("before")
                .triggeringEvent("insert")
                .eventObjectSchema(eventObjectSchema)
                .eventObjectTable(eventObjectTable)
                .actionOrientation("row")
                .srcTriggerBody(tarTriggerBody)
                .tarTriggerBody(tarTriggerBody)
                .build();
        triggerDtos.add(triggerDto);
        return triggerDtos;
    }
}
